﻿/*
Created		01/04/2012
Created by: liemqv
Modified	13/04/2012
Modify by:	liemqv
Project		HOU - eLearning	
Company		FITHOU
Version		1.0
Database	MS SQL 2005 
*/

USE master
GO

IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'hou_elearning'
)
DROP DATABASE [hou_elearning]
GO

CREATE DATABASE [hou_elearning]
GO

use [hou_elearning]
go

--Bảng người dùng
Create table [tblNguoidung]
(
	[PK_iNguoidungID] Bigint Identity primary key,
	[sTendangnhap] Varchar(50) NOT NULL, UNIQUE ([sTendangnhap]),
	[sMatkhau] Varchar(50) NOT NULL,
	[sHoten] Nvarchar(50) NOT NULL,
	[sEmail] Varchar(50) NOT NULL, UNIQUE ([sEmail]),
	[sSodienthoai] Varchar(15) NULL,
	[dNgaysinh] Datetime NULL,
	[bGioitinh] Bit NOT NULL,
	[sDiachi] Nvarchar(100) NULL,
	sGhichu NVarchar(200),
	[bAnhthe] Varbinary(max) NULL
) 
go
--ALTER table tblNguoidung add sGhichu NVarchar(200)
--ALTER table tblNguoidung add iDanhgia Integer Default 0

--Bảng đánh giá người dùng
Create table tblDanhgia_Nguoidung
(
	PK_iDanhgianguoidungID Bigint Identity Primary key,
	FK_iNguoidanhgiaID Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	FK_iNguoiduocdanhgiaID Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	dThoidiemdanhgia Datetime Default GETDATE()
)
GO

--Bảng chứng chỉ
Create table [tblChungchi]
(
	[PK_iChungchiID] Smallint Identity Primary key,
	[sTenchungchi] Nvarchar(100) NOT NULL UNIQUE,
	[sKyhieu] Varchar(20) NOT NULL UNIQUE,
	[bLogo] Varbinary(max) NULL,
	[sTendonvicap] Nvarchar(200) NOT NULL,
	[iThangdiem] Smallint NOT NULL Default 10,
	[iThoihan] Tinyint Default 0 NOT NULL
) 
go
--Bảng chứng chỉ - học viên
Create table [tblChungchi_Hocvien]
(
	PK_iChungchi_HocvienID Bigint Identity Primary key,
	FK_iChungchiID Smallint NOT NULL References tblChungchi(PK_iChungchiID),
	FK_iHocvienID Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	sSochungchi Varchar(20) NOT NULL UNIQUE,
	dNgaycap Datetime NOT NULL Default GETDATE(),
	iTrangthai Smallint NOT NULL Default 1
)
go

--Bảng xếp loại
Create table [tblXeploaichungchi]
(
	FK_iChungchiID Smallint NOT NULL References tblChungchi(PK_iChungchiID),
	iSothutu Smallint NOT NULL,
	iSodiem Smallint NOT NULL,
	sTenxeploai NVarchar(100) NOT NULL
) 
go

--Bảng danh mục tài nguyên
Create table [tblDanhmuctainguyen]
(
	[PK_iDanhmuctainguyenID] Smallint Identity primary key,
	[sTendanhmuctainguyen] Nvarchar(100) NOT NULL,
	[sTenthumuc] Varchar(50) NOT NULL
) 
go

--Bảng tài nguyên
Create table [tblTainguyen]
(
	[PK_iTainguyenID] Bigint Identity Primary key,
	[sTentainguyen] Nvarchar(150) NOT NULL,
	[sTentep] Varchar(200) NOT NULL,
	[dNgaytao] Datetime NOT NULL Default GETDATE(),
	[FK_iDanhmuctainguyenID] Smallint NOT NULL References tblDanhmuctainguyen(PK_iDanhmuctainguyenID),
	[FK_iNguoitaoID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iNguoicapnhatID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[dNgaycapnhat] Datetime NOT NULL Default GETDATE(),
) 
go

--Khóa học
Create table [tblKhoahoc]
(
	[PK_iKhoahocID] Bigint Identity primary key,
	[sTenkhoahoc] Nvarchar(100) NOT NULL,
	sKyhieu Varchar(20),
	[sGhichu] Nvarchar(1000) NULL,
	[iSoluongdukien] Smallint NOT NULL,
	iTrangthai smallint NULL Default 1,
	[dNgaytao] Datetime NOT NULL Default GETDATE(),
	[dNgaycapnhat] Datetime NOT NULL Default GETDATE(),
	[FK_iNguoitaoID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iNguoicapnhatID] Bigint References tblNguoidung(PK_iNguoidungID),
	[FK_iTainguyenminhhoa] Bigint NULL REFERENCES tblTainguyen(PK_iTainguyenID),
	[FK_iGiangvienID] Bigint NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iChungchiID] Smallint NOT NULL References tblChungchi(PK_iChungchiID)
) 
go
--ALTER table tblKhoahoc add iTrangthai smallint NULL Default 1
--ALTER table tblKhoahoc add iDanhgia Integer NULL Default 0
--ALTER table tblKhoahoc add sKyhieu Varchar(20)
--ALTER table tblKhoahoc add [FK_iNguoicapnhatID] Bigint References tblNguoidung(PK_iNguoidungID)


--Bảng đánh giá khóa học
Create table tblDanhgia_Khoahoc
(
	PK_iDanhgiakhoahocID Bigint Identity Primary key,
	FK_iNguoidanhgiaID Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	FK_iKhoahocID Bigint NOT NULL References tblKhoahoc(PK_iKhoahocID),
	dThoidiemdanhgia Datetime Default GETDATE()
)


--Bảng đăng ký khóa học
Create table [tblDangkykhoahoc]
(
	[PK_iDangkykhoahocID] Bigint Identity Primary key,
	[FK_iKhoahocID] Bigint NOT NULL References tblKhoahoc(PK_iKhoahocID),
	[FK_iHocvienID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[dNgaydangky] Datetime NOT NULL Default GETDATE(),
	[iTrangthai] Smallint NOT NULL Default 0
) 
go

--Bảng khóa học - học viên
Create table [tblKhoahoc_Hocvien]
(
	[FK_iKhoahocID] Bigint NOT NULL References tblKhoahoc(PK_iKhoahocID),
	[FK_iHocvienID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	iTrangthai Smallint NOT NULL Default 1,
	sGhichu Nvarchar(200) NULL,
	Primary key(FK_iKhoahocID, FK_iHocvienID)
) 
go

--Bảng hình thức kiểm tra
Create table [tblHinhthuckiemtra]
(
	[PK_iHinhthuckiemtraID] Smallint Identity Primary key,
	[sTenhinhthuckiemtra] Nvarchar(100) NOT NULL,
	[sKyhieu] Varchar(20) NOT NULL UNIQUE
) 
go

--Bảng danh mục kiểm tra
Create table [tblDanhmuckiemtra]
(
	[PK_iDanhmuckiemtraID] Smallint Identity Primary key,
	[sTendanhmuc] Nvarchar(100) NOT NULL,
	[sKyhieu] Varchar(20) NOT NULL UNIQUE,
	[iHesodiem] Smallint NOT NULL
) 
go

--Bảng bài học
Create table [tblBaihoc]
(
	[PK_iBaihocID] Bigint Identity Primary key,
	[sTenbaihoc] Nvarchar(200) NOT NULL,
	[sThongtinbaihoc] Nvarchar(500) NULL,
	[iTrangthai] Smallint NOT NULL,
	[dNgaytao] Datetime NOT NULL Default GETDATE(),
	[dNgaycapnhat] Datetime NULL Default GETDATE(),
	FK_iAnhminhhoaID Bigint NULL References tblTainguyen(PK_iTainguyenID),
	[FK_iNguoitaoID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iNguoicapnhat] Bigint NULL References tblNguoidung(PK_iNguoidungID)
) 
go
--ALTER table tblBaihoc drop column FK_iTainguyenID
--ALTER table tblBaihoc drop column [iSothutu]
--ALTER table [tblBaihoc] add iDanhgia Integer NULL Default 0
--ALTER table [tblBaihoc] add FK_iAnhminhhoaID Bigint NULL References tblTainguyen(PK_iTainguyenID)


--Bảng đánh giá bài học
Create table tblDanhgia_Baihoc
(
	PK_iDanhgiabaihocID Bigint Identity Primary key,
	FK_iNguoidanhgiaID Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	FK_iBaihocID Bigint NOT NULL References tblBaihoc(PK_iBaihocID),
	dThoidiemdanhgia Datetime Default GETDATE()
)

--Bảng slide
Create table tblSlide
(
	PK_iSlideID Bigint Identity Primary key,
	sTenslide NVarchar(100),
	iTrangthai Smallint Default 1,
	sGhichu NVarchar(200),
	FK_iAnhminhhoaID Bigint NULL References tblTainguyen(PK_iTainguyenID),
	FK_iNguoitaoID Bigint References tblNguoidung(PK_iNguoidungID),
	FK_iNguoicapnhat Bigint References tblNguoidung(PK_iNguoidungID),
	dThoidiemtao Datetime,
	dThoidiemcapnhat Datetime
)
GO

--Bảng Trang slide
CREATE table tblTrangslide
(
	PK_iTrangslideID Bigint Identity Primary key,
	FK_iSlideID Bigint References tblSlide(PK_iSlideID),
	iSothutu Smallint,
	sTieude NVarchar(100),
	sGhichu NVarchar(200)
)
GO

--Bảng danh mục sự kiện slide
Create table tblDanhmucsukienslide
(
	PK_sDanhmucsukienslideID NVarchar(20) Primary key,
	sTensukien NVarchar(100),
	sGhichu NVarchar(200)
)
GO

--Bảng sự kiện slide
Create table tblSukienslide
(
	PK_iSukienslideID Bigint Identity Primary key,
	iSothutu Smallint Default SCOPE_IDENTITY(), --Số thứ tự
	iThoigianhienthi Integer Default 5, --Thời gian hiển thị (đơn vị: giây)
	sNoidung Nvarchar(1000),
	FK_sDanhmucsukienslideBatdauID NVarchar(20) References tblDanhmucsukienslide(PK_sDanhmucsukienslideID),
	FK_sDanhmucsukienslideKetthucID NVarchar(20) References tblDanhmucsukienslide(PK_sDanhmucsukienslideID),
	FK_iTrangslideID Bigint References tblTrangslide(PK_iTrangslideID)
)
GO

--Bảng bài học - slide
Create table tblBaihoc_Slide
(
	PK_iBaihoc_SlideID Bigint Identity Primary key,
	FK_iBaihocID Bigint NOT NULL References tblBaihoc(PK_iBaihocID),
	FK_iSlideID Bigint NOT NULL References tblSlide(PK_iSlideID),
	iSothutu Smallint
)
go

--Bảng khóa học - bài học
Create table [tblKhoahoc_Baihoc]
(
	PK_iKhoahoc_BaihocID Bigint Identity Primary key,
	FK_iKhoahocID Bigint NOT NULL References tblKhoahoc(PK_iKhoahocID),
	FK_iBaihocID Bigint NOT NULL References tblBaihoc(PK_iBaihocID),
	iSothutu smallint Default 0,
	sGhichu NVarchar(200) NULL
) 
go
--ALTER table tblKhoahoc_Baihoc add iSothutu smallint Default 0

--Bảng điểm danh
Create table [tblDiemdanh]
(
	PK_iDiemdanhID Bigint Identity Primary key,
	FK_iKhoahoc_BaihocID Bigint References tblKhoahoc_Baihoc(PK_iKhoahoc_BaihocID),
	FK_iTrangslideID Bigint References tblTrangslide(PK_iTrangslideID),
	[FK_iHocvienID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[dThoidiemxem] Datetime Default GETDATE(),
	[iTrangthai] Smallint Default 0
) 
go

--Bảng danh mục câu hỏi
Create table [tblDanhmuccauhoi]
(
	[PK_iDanhmuccauhoiID] Smallint Identity Primary key,
	[sTendanhmuccauhoi] Nvarchar(100) NOT NULL
) 
go

--Bảng câu hỏi
Create table [tblCauhoi]
(
	[PK_iCauhoiID] Bigint Identity Primary key,
	[sNoidung] Nvarchar(MAX) NOT NULL,
	[iDiem] Smallint Default 1 NOT NULL,
	[dNgaytao] Datetime Default GETDATE() NOT NULL,
	[dNgaycapnhat] Datetime Default GETDATE() NOT NULL,
	[FK_iDanhmuccauhoiID] Smallint NOT NULL References tblDanhmuccauhoi(PK_iDanhmuccauhoiID),
	[FK_iNguoitaoID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iNguoicapnhatID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID)
) 
go

--Bảng phương án lựa chọn
Create table [tblPhuongan]
(
	[PK_iPhuonganID] Bigint Identity Primary key,
	[sNoidung] Nvarchar(2000) NOT NULL,
	[bDung] Bit NOT NULL,
	[dNgaytao] Datetime Default GETDATE() NOT NULL,
	[dNgaycapnhat] Datetime Default GETDATE() NOT NULL,
	[FK_iNguoitao] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iNguoicapnhat] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iDanhmuccauhoiID] Smallint NOT NULL References tblDanhmuccauhoi(PK_iDanhmuccauhoiID)
) 
go

--Bảng câu hỏi - phương án
Create table [tblCauhoi_Phuongan]
(
	[FK_iCauhoiID] Bigint NOT NULL References tblCauhoi(PK_iCauhoiID),
	[FK_iPhuonganID] Bigint NOT NULL References tblPhuongan(PK_iPhuonganID),
	Primary key(FK_iCauhoiID, FK_iPhuonganID)
) 
go

--Bảng hỏi đáp
Create table [tblHoidap]
(
	[PK_iHoidapID] Bigint Identity Primary key,
	[FK_iBaihocID] Bigint NOT NULL References tblBaihoc(PK_iBaihocID),
	[FK_iGiangvienID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iHocvienID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	sNoidung Nvarchar(300) NOT NULL,
	dNgayhoidap Datetime NOT NULL Default GETDATE()
) 
go

--Bảng lịch học
Create table [tblLichhoc]
(
	[PK_iLichhocID] Bigint Identity Primary key,
	[FK_iKhoahoc_BaihocID] Bigint NOT NULL References tblKhoahoc_Baihoc(PK_iKhoahoc_BaihocID),
	[FK_iNguoitao] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[dNgaytao] Datetime NOT NULL Default GETDATE(),
	[FK_iNguoicapnhat] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[dNgaycapnhat] Datetime NOT NULL Default GETDATE(),
	[dNgayhoc] Datetime NOT NULL,
	[dGiobatdau] Datetime NOT NULL,
	[dGioketthuc] Datetime NOT NULL,
	[sGhichu] Nvarchar(200) NULL,
	[iTrangthai] Smallint Default 1 NOT NULL
) 
go

--Bảng nhật ký học
Create table [tblNhatkyhoc]
(
	[PK_iNhatkyhocID] Bigint Identity Primary key,
	[FK_iKhoahocID] Bigint NOT NULL References tblKhoahoc(PK_iKhoahocID),
	[FK_iBaihocID] Bigint NOT NULL References tblBaihoc(PK_iBaihocID),
	[dNgayhoc] Datetime NOT NULL,
	[sGhichu] Nvarchar(200) NULL
) 
go

--Bảng nhóm người dùng
Create table tblNhomnguoidung
(
	PK_iNhomnguoidungID Smallint Identity Primary key,
	sTennhom NVarchar(100),
	sGhichu NVarchar(200)
)

--Bảng Người dùng - Nhóm
Create table tblNguoidung_nhom
(
	FK_iNguoidungID Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	FK_iNhomnguoidungID Smallint NOT NULL References tblNhomnguoidung(PK_iNhomnguoidungID),
	Primary key(FK_iNguoidungID, FK_iNhomnguoidungID)
)

--Bảng quyền
Create table [tblQuyen]
(
	[PK_iQuyenID] Smallint Identity Primary key,
	[sTenquyen] Nvarchar(100) NOT NULL,
	[sTenviettat] Varchar(10) NOT NULL UNIQUE,
	[sGhichu] Nvarchar(200) NULL
) 
go

--Bảng Nhóm người dùng - quyền
Create table [tblNhomnguoidung_Quyen]
(
	[FK_iNhomnguoidungID] Smallint NOT NULL References tblNhomnguoidung(PK_iNhomnguoidungID),
	[FK_iMaquyenID] Smallint NOT NULL References tblQuyen(PK_iQuyenID),
	Primary key(FK_iNhomnguoidungID, FK_iMaquyenID)
) 
go

--Bảng tác vụ
Create table [tblTacvu]
(
	[PK_iTacvuID] Integer Identity Primary key,
	[sTentacvu] Nvarchar(150) NOT NULL,
	[sKyhieu] Varchar(20) NOT NULL UNIQUE,
	[sGhichu] Nvarchar(200) NULL
) 
go

--Bảng chức năng
Create table [tblChucnang]
(
	[PK_iChucnangID] Integer Identity Primary key,
	[sTenchucnang] Nvarchar(150) NOT NULL,
	[sKyhieu] Varchar(20) NOT NULL UNIQUE,
	[sGhichu] Nvarchar(200) NULL
) 
go

--Bảng Chức năng - tác vụ
Create table [tblChucnang_Tacvu]
(
	[PK_iChucnang_TacvuID] Bigint Identity Primary key,
	[FK_iChucnangID] Integer NOT NULL References tblChucnang(PK_iChucnangID),
	[FK_iTacvuID] Integer NOT NULL References tblTacvu(PK_iTacvuID),
	[sGhichu] Nvarchar(200) NOT NULL
)
go

--Bảng Quyền - Chức năng - Tác vụ
Create table [tblQuyen_Chucnang_Tacvu]
(
	[FK_iChucnang_TacvuID] Bigint NOT NULL References tblChucnang_Tacvu(PK_iChucnang_TacvuID),
	[FK_iQuyenID] Smallint NOT NULL References tblQuyen(PK_iQuyenID),
	Primary key(FK_iChucnang_TacvuID, FK_iQuyenID)
) 
go

--Bảng đề kiểm tra
Create table [tblDekiemtra]
(
	[PK_iDekiemtraID] Bigint Identity Primary key,
	[sTendekiemtra] Nvarchar(150) NOT NULL,
	[sGhichu] Nvarchar(200) NULL,
	[dNgaytao] Datetime Default GETDATE() NOT NULL,
	[dNgaycapnhat] Datetime Default GETDATE() NOT NULL,
	[FK_iNguoitaoID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iNguoicapnhatID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iHinhthuckiemtraID] Smallint NOT NULL References tblHinhthuckiemtra(PK_iHinhthuckiemtraID)
) 
go

--Bảng câu hỏi - đề kiểm tra
Create table [tblCauhoi_Dekiemtra]
(
	[FK_iDekiemtraID] Bigint NOT NULL References tblDekiemtra(PK_iDekiemtraID),
	[FK_iCauhoiID] Bigint NOT NULL References tblCauhoi(PK_iCauhoiID),
	Primary key(FK_iDekiemtraID, FK_iCauhoiID)
) 
go

--Bảng kiểm tra
Create table [tblKiemtra]
(
	[PK_iKiemtraID] Bigint Identity Primary key,
	[sTenbaikiemtra] Nvarchar(150) NOT NULL,
	[FK_iKhoahocID] Bigint NOT NULL References tblKhoahoc(PK_iKhoahocID),
	[FK_iDekiemtraID] Bigint NOT NULL References tblDekiemtra(PK_iDekiemtraID),
	[FK_iHinhthuckiemtraID] Smallint NOT NULL References tblHinhthuckiemtra(PK_iHinhthuckiemtraID),
	[FK_iDanhmuckiemtraID] Smallint NOT NULL References tblDanhmuckiemtra(PK_iDanhmuckiemtraID),
	[iTrangthai] Smallint Default 0 NOT NULL,
	[dNgaykiemtra] Datetime NOT NULL,
	[dGiobatdaukiemtra] Datetime NOT NULL,
	[iThoigianlambai] Smallint NOT NULL
) 
go

--Bảng học viên - kiểm tra
Create table [tblHocvien_Kiemtra]
(
	[FK_iHocvienID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iKiemtraID] Bigint NOT NULL References tblKiemtra(PK_iKiemtraID),
	[sMatkhau] Varchar(6) NOT NULL,
	[iTrangthai] Smallint NOT NULL Default 0,
	Primary key(FK_iHocvienID, FK_iKiemtraID)
) 
go

--Bảng thông tin bài kiểm tra
Create table [tblThongtinbaikiemtra]
(
	[PK_iThongtinbaikiemtraID] Bigint Identity Primary key,
	[FK_iHocvienID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[FK_iKiemtraID] Bigint NOT NULL References tblKiemtra(PK_iKiemtraID),
	[fDiem] float Default 0 NULL,
	[iTrangthai] Smallint Default 1 NOT NULL,
	[dThoigiannopbai] Datetime NULL Default GETDATE(),
	[sNhanxet] Nvarchar(MAX) NULL,
	[FK_iGiangvienchambaiID] Bigint NULL References tblNguoidung(PK_iNguoidungID)
) 
go

--Bảng bài kiểm tra tự luận
Create table [tblBaikiemtratuluan]
(
	[FK_iThongtinbaikiemtraID] Bigint NOT NULL References tblThongtinbaikiemtra(PK_iThongtinbaikiemtraID),
	[FK_iCauhoiID] Bigint NOT NULL References tblCauhoi(PK_iCauhoiID),
	[sNoidung] Nvarchar(MAX) NOT NULL,
	[fDiem] float NULL,
	[sNhanxet] NVarchar(MAX) NULL,
	Primary key(FK_iThongtinbaikiemtraID, FK_iCauhoiID)
) 
go

--Bảng bài kiểm tra trắc nghiệm
Create table [tblBaikiemtratracnghiem]
(
	[FK_iThongtinbaikiemtraID] Bigint NOT NULL References tblThongtinbaikiemtra(PK_iThongtinbaikiemtraID),
	[FK_iPhuonganID] Bigint NOT NULL References tblPhuongan(PK_iPhuonganID),
	Primary key(FK_iThongtinbaikiemtraID, FK_iPhuonganID)
) 
go


--Bảng liên hệ
Create table [tblLienhe]
(
	[PK_iLienheID] Bigint Identity Primary key,
	[sNoidung] Nvarchar(1000) NOT NULL,
	[dNgaygiolienhe] Datetime Default GETDATE() NOT NULL,
	[sEmail] Varchar(50) NOT NULL
) 
go

--Bảng bài viết
Create table [tblBaiviet]
(
	[PK_iBaivietID] Bigint Identity Primary key,
	[FK_iNguoivietID] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	sTenbaiviet Nvarchar(100) NOT NULL,
	sTieude Nvarchar(100) NOT NULL,
	sGhichu Nvarchar(150),
	[sNoidung] Nvarchar(MAX) NOT NULL,
	[FK_iTainguyenID] Bigint NULL References tblTainguyen(PK_iTainguyenID),
	[dNgaydang] Datetime Default GETDATE() NOT NULL,
	[iTrangthai] Smallint Default 1 NOT NULL,
	[FK_iNguoicapnhat] Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	[dNgaycapnhat] Datetime NOT NULL Default GETDATE()
) 
go

--Bảng đơn vị quản lý
Create table [tblDonviquanly]
(
	PK_iDonviquanlyID Bigint Identity Primary key,
	sTendonviquanly Nvarchar(100) NOT NULL,
	sDiachi NVarchar(200),
	sSodienthoai Varchar(15),
	FK_iThutruongID Bigint NOT NULL References tblNguoidung(PK_iNguoidungID),
	sChucvuthutruong NVarchar(50) NOT NULL
)
go
